package dao;

import beans.AnimalVaca;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class AnimalVacaDAO {

    PreparedStatement pst;
    ResultSet rs;

    public AnimalVacaDAO() {
    }

    public AnimalVaca load(int ano, int mes, int numBrinco) {
        AnimalVaca vaca = new AnimalVaca();

        String query = "SELECT * FROM vaca NATURAL JOIN animal WHERE ano = AND mes = ? AND numBrinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, ano);
            pst.setInt(2, mes);
            pst.setInt(3, numBrinco);
            
            rs = pst.executeQuery();

            if (rs.next()) {
                vaca.setAno(rs.getInt("ano"));
                vaca.setMes(rs.getInt("mes"));
                vaca.setNumBrinco(rs.getInt("num_brinco"));
                vaca.setCheia(rs.getBoolean("cheia"));
                vaca.setRaca(rs.getString("raca"));
                vaca.setPeso(rs.getFloat("peso"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return vaca;
    }

    public ArrayList<AnimalVaca> loadRebanho(int mes, int ano) {
        ArrayList<AnimalVaca> vacas = new ArrayList<AnimalVaca>();

        String query = "SELECT * FROM vaca NATURAL JOIN animal WHERE mes = ? AND ano = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, mes);
            pst.setInt(2, ano);
                      
            rs = pst.executeQuery();

            while (rs.next()) {
                AnimalVaca vaca = new AnimalVaca();

                vaca.setAno(rs.getInt("ano"));
                vaca.setMes(rs.getInt("mes"));
                vaca.setNumBrinco(rs.getInt("num_brinco"));
                vaca.setCheia(rs.getBoolean("cheia"));
                vaca.setRaca(rs.getString("raca"));
                vaca.setPeso(rs.getFloat("peso"));

                vacas.add(vaca);
            }
        } catch (SQLException ex) {
            ex.getLocalizedMessage();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }

        return vacas;
    }

    public void insert(AnimalVaca vaca) {
        String query = "BEGIN;";
        query += "INSERT INTO animal (ano, mes, num_brinco, raca, peso) VALUES (?, ?, ?, ?, ?);";
        query += "INSERT INTO vaca (ano, mes, num_brinco, cheia) VALUES (?, ?, ?, ?);";
        query += "COMMIT;";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setInt(1, vaca.getAno());
            pst.setInt(2, vaca.getMes());
            pst.setInt(3, vaca.getNumBrinco());
			pst.setString(4, vaca.getRaca());
			pst.setFloat(5, vaca.getPeso());
            pst.setInt(6, vaca.getAno());
            pst.setInt(7, vaca.getMes());
            pst.setInt(8, vaca.getNumBrinco());
            pst.setBoolean(9, vaca.isCheia());

            pst.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private void update(AnimalVaca vaca) {
        String query = "BEGIN;";
        query += "UPDATE animal SET ano = ?, mes = ?, num_brinco = ?, raca = ?, peso = ? WHERE ano = ? AND mes = ? and num_brinco = ?";
        query += "UPDATE vaca SET ano = ?, mes = ?, num_brinco = ?, cheia = ? WHERE ano = ? AND mes = ? AND num_brinco = ?";
        query += "COMMIT;";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setInt(1, vaca.getAno());
            pst.setInt(2, vaca.getMes());
            pst.setInt(3, vaca.getNumBrinco());
            pst.setString(4, vaca.getRaca());
            pst.setFloat(5, vaca.getPeso());
            pst.setInt(6, vaca.getAno());
            pst.setInt(7, vaca.getMes());
            pst.setInt(8, vaca.getNumBrinco());
            
            pst.setInt(9, vaca.getAno());
            pst.setInt(10, vaca.getMes());
            pst.setInt(11, vaca.getNumBrinco());
            pst.setBoolean(12, vaca.isCheia());
            pst.setInt(14, vaca.getAno());
            pst.setInt(14, vaca.getMes());
            pst.setInt(15, vaca.getNumBrinco());

            pst.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }


    }

    public void save(AnimalVaca vaca) {
        String query = "SELECT COUNT(*) FROM vaca WHERE ano = ? AND mes = ? AND num_brinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, vaca.getAno());
            pst.setInt(2, vaca.getMes());
            pst.setInt(3, vaca.getNumBrinco());

            rs = pst.executeQuery();
            rs.next();

            if (rs.getInt("count") != 0) {
                update(vaca);
            } else
                insert(vaca);

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

    public void delete(AnimalVaca vaca) {
        String query = "BEGIN;";
        query += "DELETE FROM animal WHERE ano = ? AND mes = ? AND num_brinco = ?;";
        query += "DELETE FROM vaca WHERE ano = ? AND mes = ? AND num_brinco = ?;";
        query += "COMMIT;";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, vaca.getAno());
            pst.setInt(2, vaca.getMes());
            pst.setInt(3, vaca.getNumBrinco());
            
            pst.setInt(4, vaca.getAno());
            pst.setInt(5, vaca.getMes());
            pst.setInt(6, vaca.getNumBrinco());

            pst.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

}
